Work From Home, Part 5 – Accurate Names

Notes Dec 2, 2022 — All Active Directory information has been merged into the main documentation for Setting up Groups using Active Directory, so supersedes this.

With the current emphasis on working from home and all the changes that incurs, maybe one of these questions is now suddenly more important to you than it used to be!

Today’s topic

• How can I see the names for mobile calls from our employees?

Other pages this week:

• Is mobility working right, are people receiving phone calls?
• How many folks are using Jabber or softphones?
• What gateways do I have that are reaching capacity?
• What gateways do I have that are now far *under* capacity?

Naming Names…

Today’s topic is a little different from the previous ones this week. Today, we’re going to work on some plumbing that will enhance other reports – adding names into your records so you don’t have empty spaces like this:

There’s a couple of ways to do this –

1. Manually adding them to the groups using our Setup->Define Groups (optional) page.
2. Use our Setup->Define Groups (optional) page to download/​upload a CSV generated elsewhere.
3. Retrieve this information from Active Directory and have the groups populated automatically.

I know, you heard me say ​“Groups” but you don’t want groups!

That’s OK – the groups/​extensions feature is actually more generic than that and just maps names, groups and subgroups to numbers. That way whenever, say, 7158917420 shows up in your records, you’ll have a corresponding name ​“Rich” to go with that. Commonly this is for actual grouping purposes and our Browse Extensions page, but you can use them everywhere once set up.

Using groups manually

The easiest way is to follow along our directions for using groups and extensions in our user documentation. But it’s so simple I’ll provide a summary here:

First, find a few good numbers to test with and find the names associated with them.

Click Setup->Define groups (optional)

Then head over to the tab Add New Extension/​Group and enter those few numbers and names in.

If you make a mistake, just click the tab Edit/​Delete Extensions/​Groups and edit them – one line at a time being sure to click save on each one because you can’t ​“batch” changes up here.

For purposes of illustrating this, I edited a few of my extensions to match numbers on that page I had a screenshot of above…

And just like that, now the names show up in Browse Calls.

Works great! Just a little clunky and slow for anything more than a dozen or two entries. So, how can we make this better?

Downloading/​Uploading CSV files

An improvement is using a CSV file, which you can edit in Excel or probably even Google Sheets. If anyone’s still using Lotus 123 please email me a screenshot of you editing the groups.csv file with Lotus 123 at docs@​sideviewapps.​com – the first person who does gets a t‑shirt!

This method really works for pretty much any way you can get an export from another product of names and phone numbers, like CUCM, AD, LDAP, or some spreadsheet in HR. Just match the formatting and fields, and you should be able to upload any of those.

Note that I don’t recommend using it for AD (instead, see the next section to have Splunk do this *for* you!), but sometimes your AD people are stingy with connections and won’t let mere mortals talk to their precious servers. In that case you can use any of a variety of tools to export the parts of AD you need and do the CSV thing.

Usage is pretty straightforward.

• Click Setup->Define groups (optional)
• Click the tab Download CSV
• Click the little button to actually download the file.
• Save it somewhere.
• Open it in Excel, or Lotus 123 …

We’ll have a brief intermission here while you type a bunch of stuff into Excel. Once you are finished, save the file as a CSV (just make sure it isn’t a xlsx or whatever!)

• Click Setup->Define groups (optional)
• Click the tab Upload CSV
• Click the Browse… button, find the file and select it.
• Click the update button to actually download the file.
• And that’s it.

Just like before, once you’ve uploaded it the names that match those numbers will populate pretty much immediately.

Using Active Directory

Notes Dec 2, 2022 — All Active Directory information has been merged into the main documentation for Setting up Groups using Active Directory, so supersedes this.

I saved the best for last.

If you’ve already set up groups via AD

Perhaps you’ve already set up the Splunk Supporting Add-on for Active Directory as per our instructions in the Set Up Groups pages of our docs.

• If you followed our docs, you’ll see that we only retrieve the telephoneNumber field.
• This makes no name fields for mobile phones.
• Sadness abounds.
• But we’ll fix that soon! Keep reading!

If you *have* AD, but haven’t set it up yet

Or maybe you haven’t yet set up the above, but you do have Active Directory in your environment.

• In that case, follow the steps in in the Set Up Groups pages of our docs.
• You should probably follow all the way through, because that search in there explains a lot of what it does and we’ll use that as a basis for our new and improved search below!

So now that you have a working connection to AD, Oh the fun we’ll have!

Now for some magic!

So, the ​“starter kit” AD query I write about in our docs was just that. A starter search. We need way more power though and have to turn it up to 11.

Here’s what we start with:

| ldapsearch search="(&(objectClass=user) (!(objectClass=computer)))" 
 attrs="userAccountControl, telephoneNumber, displayName, department"
| rex field=telephoneNumber mode=sed "s/[^0-9+]//g" 
| where userAccountControl="NORMAL_ACCOUNT" 
 AND isnotnull(telephoneNumber) AND telephoneNumber!="" 
| rename telephoneNumber AS number, displayName AS name, department AS group
| table number, name, group, subgroup, subgroup2, subgroup3, subgroup4

That’s a great search, but the things it is missing are to add the two additional attributes (mobile and ipPhone – this can be extended if you need even more number fields), clean those of random goofy characters, then convert it from a format of

ipPhone, telephoneNumber, mobile, name, group, subgroup

into

ipPhone, name, group, subgroup
telephoneNumber, name, group, subgroup
mobile, name, group, subgroup

So let’s do that!

| ldapsearch search="(&(objectClass=user) (!(objectClass=computer)))"
 attrs="userAccountControl, telephoneNumber, mobile, ipPhone, displayName, department" 
| rex field=telephoneNumber mode=sed "s/[^0-9+]//g" 
| rex field=mobile mode=sed "s/[^0-9+]//g" 
| rex field=ipPhone mode=sed "s/[^0-9+]//g" 
| where userAccountControl="NORMAL_ACCOUNT" AND isnotnull(telephoneNumber) AND telephoneNumber!="" 
| eval number = mvdedup(mvappend(telephoneNumber, mobile, ipPhone))
| mvexpand number
| stats last(displayName) as name last(department) as group by number
| table number, name, group, subgroup, subgroup2, subgroup3, subgroup4
| outputlookup groups

So, objectives achieved!

The two additional rex’es clean those numbers of anything except + and digits.

The new group of eval, mvexpand and stats first smashes the three number fields into one multivalued field called ​“number”, just like we want, the mvexpand then separates them back into separate events, and finally the stats just sort of deduplicates the whole list (with some free renames in there so we don’t have to do them with the rename command).

References

Splunk Supporting Add-on for Active Directory, AKA ​“SA-ldapsearch”.

The Splunk stats command and if you look down the left, ALL the Splunk commands are there, so I won’t link them separately.