topic Re: How to write a search using a CSV file to display a list of lent materials by user based on status? in Splunk Search

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

erichard — Fri, 04 Mar 2016 10:56:42 GMT

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.

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

somesoni2 — Fri, 04 Mar 2016 15:15:19 GMT

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

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

erichard — Fri, 04 Mar 2016 15:29:21 GMT

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.

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

javiergn — Fri, 04 Mar 2016 15:30:41 GMT

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:

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

erichard — Fri, 04 Mar 2016 15:44:58 GMT

Thanks it's working !