How to extract and create fields from event logs and table the results?

[kavraja]

I'm running a search at the moment that lists users connecting to a vpn during out of work hours and I'm getting the right data but I just wanted to know if it's possible to sort the data displayed in the events log which do not have a field into a table. The search is something like this:

host="xx" index=xx" "Account-Name data_type="
"ip:source-ip" "Fully-Qualifed-User-Name"
(date_hour>18 OR date_hour<7) | table date_wday, date_hour, date_minute, source_ip, | sort date_wday

And an example event looks like:

JOE BLOGSXXXxxx.xxx.xx.xx

The data I want to get into a table is the Account Name and the date_hour and date_minute but the fields showing up are date_type, date_hour and so on but NO field for account name but the account name shows up in the event data.

I know how to put the results into a table using fields but I'm wondering if its possible to get data from the event log that does not have a field and put it into a table?

Thanks

[sk314]

oh well...you are welcome! splunk is very well documented!

[kavraja · Accepted solution]

I found what I was after at http://docs.splunk.com/Documentation/Splunk/6.1.3/Knowledge/ExtractfieldsinteractivelywithIFX

Used the extract fields option 🙂

[sk314]

So the account name that you want to extract in the sample event is SAM-Account-Name? Also, Is it always preceded by /Provider-Type in all your events?

[kavraja]

Yes, the SAM-Account-Name was what I wanted but I used the extract fields link you provided earlier and got it to work.

I followed the link to http://docs.splunk.com/Documentation/Splunk/6.1.3/Knowledge/ExtractfieldsinteractivelywithIFX section and followed the steps and got the results I was after. I clicked on the extract fields option and put in the examples values I was looking for and after testing, it works fine 🙂

Thanks sk314 for the quick responses and pointing me in the right direction. Much appreciated.

[sk314]

I still don't see the source_ip in your example data. If you could post data from multiple events, things will be clearer.

[kavraja]

Theres a few formatting issues but here's an example

User-Name data_type="1"JOE BLOGS /User-Name Called-Station-Id data_type="1" xx.xx.xx.xx /Called-Station-Id Calling-Station-Id data_type="1" xx.xx.xx.xx.xx /Calling-Station-Id Client-IP-Address data_type="3" xx.xx.xx.xx.xx/Client-IP-Address Cisco-AV-Pair data_type="1" ip:source-ip=xx.xx.xx.xx.xx.xx /Cisco-AV-Pair Proxy-Policy-Name data_type="1" Use Windows authentication for all users /Proxy-Policy-Name Provider-Type data_type="0" 1 /Provider-Type SAM-Account-Name data_type="1" JOE BLOGS /SAM-Account-Name

[sk314]

You need to extract fields during search. For more info: http://docs.splunk.com/Documentation/Splunk/6.1.3/Knowledge/Addfieldsatsearchtime

Also, If you could post sample event data, I'm sure we can help you with that too.

[kavraja]

Sorry about that, Forgot to put in some sample data. The data I get is:

User-Name data_type="x" JOE BLOGS /User-Name Client-IP-Address data_type="x"=xxx.xx.xx.xx /Client-IP-Address Cisco-AV-Pair data_type="x" = ip:source-ip=xx.xx.xx.xx /Cisco-AV-Pair

I can get the source ip from the interesting fields but would like to also get the user name into a table