Splunk Search

How to write a search using a CSV file to display a list of lent materials by user based on status?

erichard
Explorer

Hello,

I have a list of assets like this:

date,material,username,status
01/12/15,"IPad #4654654",eric,lent
01/12/15,"Iphone #4547879",paul,lent
01/15/15,"IPad #4654654",eric,return
01/16/15,"Keyboard #454456",eric,lent
01/17/15,"Nexus 7 #414456",eric,lent

and I would like to extract the list of materials that are actually lent by user. In this case:

eric   Keyboard   #454456    01/16/15
       Nexus 7    #414456    01/17/15
paul   Iphone     #4547879   01/12/15

How I can do this?

Thanks in advance for your help.

0 Karma
1 Solution

javiergn
Super Champion

Try this:

| inputcsv mycsv.csv
| eval statusInteger = if(match(status,"lent"), 1, -1)
| eval date = strptime(date, "%m/%d/%y")
| stats sum(statusInteger) as status, max(date) as date by username, material
| where status != 0 
| fields - status
| fieldformat date=strftime(date, "%m/%d/%y")
| stats list(material) as material, list(date) as date by username

Output:

alt text

View solution in original post

0 Karma

javiergn
Super Champion

Try this:

| inputcsv mycsv.csv
| eval statusInteger = if(match(status,"lent"), 1, -1)
| eval date = strptime(date, "%m/%d/%y")
| stats sum(statusInteger) as status, max(date) as date by username, material
| where status != 0 
| fields - status
| fieldformat date=strftime(date, "%m/%d/%y")
| stats list(material) as material, list(date) as date by username

Output:

alt text

0 Karma

erichard
Explorer

Thanks it's working !

0 Karma

somesoni2
Revered Legend

Assuming you already have the fields extracted from your csv type data, try something like this

your base search status=lent | stats list(material) as material list(date) as date by username
0 Karma

erichard
Explorer

Thanks for your answer, but it's not working as i need, with your answer i have :

eric IPad #4654654
Keyboard #454456
Nexus 7 #414456

The material IPad has beed returned (01/15/15,"IPad #4654654",eric,return) however it shouldn't appear.

0 Karma
Get Updates on the Splunk Community!

Splunk Answers Content Calendar, July Edition I

Hello Community! Welcome to another month of Community Content Calendar series! For the month of July, we will ...

Secure Your Future: Mastering Upgrade Readiness for Splunk 10

Spotlight: The Splunk Health Assistant Add-On  The Splunk Health Assistant Add-On is your ultimate companion ...

Observability Unlocked: Kubernetes & Cloud Monitoring with Splunk IM

Ready to master Kubernetes and cloud monitoring like the pros? Join Splunk’s Growth Engineering team on ...